﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace ProjectVLIB.DA
{
    class DataAccess
    {

        private string connectString = @"Data Source=.\SQLEXPRESS;AttachDbFilename=d:\Inet\inet13\TinhNQ\LAB\ProjectVLIB\ESHOPPER.mdf;Integrated Security=True;User Instance=True";
        private SqlConnection conn;
        private SqlDataAdapter da;
        private SqlCommand cmd;

        void OpenConnect()
        {
            if (conn == null)
            {
                conn = new SqlConnection(connectString);
            }
            //--------------------------
       
            if (conn != null && conn.State == ConnectionState.Closed)
            {
                conn.Open();
            }
        }
        //-------------------------------------


        void CloseConnect()
        {
            if (conn != null && conn.State == ConnectionState.Open)
            {
                conn.Close();
            }
        }
   
        public DataTable GetData(string sql)
        {
            OpenConnect();
            cmd = new SqlCommand(sql, conn);
            da = new SqlDataAdapter(cmd);
            DataTable dt = new DataTable();
            da.Fill(dt);
            da.Dispose();
            CloseConnect();
            return dt;

        }

        public DataTable GetData(string sql, SqlParameter[] param)
        {
            OpenConnect();
            cmd = new SqlCommand(sql, conn);
            cmd.Parameters.AddRange(param);
            da = new SqlDataAdapter(cmd);

            DataTable dt = new DataTable();
            da.Fill(dt);
            da.Dispose();

            CloseConnect();
            return dt;
        }

        public int GetNumberData(string sql)
        {
            OpenConnect();
            SqlCommand cmd = new SqlCommand(sql, conn);
            int count = int.Parse(cmd.ExecuteScalar().ToString());
            CloseConnect();

            return count;
        }

        public string GetStringData(string sql)
        {
            OpenConnect();
            SqlCommand cmd = new SqlCommand(sql, conn);
            string str = cmd.ExecuteScalar().ToString();
            CloseConnect();

            return str;
        }

        public bool Execute(string sql)
        {
            OpenConnect();
            SqlCommand cmd = new SqlCommand(sql, conn);
            cmd.ExecuteNonQuery();
            CloseConnect();
            return true;
        }

        public DataTable GetDataLoaiHang()
        {
          
            string sql = "select * from tbl_LoaiHang ";
        
            DataTable dt = new DataTable();
            dt = GetData(sql);
            return dt;
        }

        public DataTable GetDataLoaiHang(string maLoaiHang)
        {
            string connectString = @"Data Source=.\SQLEXPRESS;AttachDbFilename=d:\Inet\inet13\TinhNQ\LAB\ProjectVLIB\ESHOPPER.mdf;Integrated Security=True;User Instance=True";
            SqlConnection conn = new SqlConnection(connectString);
            conn.Open();

            string sql = "select * from tbl_LoaiHang WHERE MaLoaiHang = @MaLoaiHang";

            SqlParameter[] param = new SqlParameter[1];
            param[0] = new SqlParameter("@MaLoaiHang", SqlDbType.Int);
            param[0].Value = maLoaiHang;

            DataTable dt = new DataTable();
            dt = GetData(sql, param);

            return dt;
        }


        public int GetCountLoaiHang()
        {
           
            string sql = "select count(*) from tbl_LoaiHang";
            int count = GetNumberData(sql);

            return count;
        }


        public bool InsertLoaiHang(string tenLoaiHang)
        {
            string sql = "INSERT INTO tbl_LoaiHang (TenLoaiHang) VALUES (N'" + tenLoaiHang + "')";
            Execute(sql);
            
            return true;
        }



    }
}
